package com.bdqn.tour.dao.impl;


import com.bdqn.tour.dao.HotelImplementationClass;
import com.bdqn.tour.dao.uerDao;
import com.bdqn.tour.entity.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class ImplUersDao extends uerDao implements HotelImplementationClass {

/**
 * 添加方法
 * @param users Comment对像
 * @return 返回是否存在（如存在返回1；则返回0）
 *
 */
    @Override
    public int addUsers(Comment users) {
        Connection conn = this.getCollection();
        PreparedStatement psmt = null;
        ResultSet res = null;
        int result = 0;
        // 获取当前时间
        LocalDateTime currentTime = LocalDateTime.now();

        // 定义时间格式
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        // 格式化当前时间
        String formattedTime = currentTime.format(formatter);
        try {
            String sql = "INSERT INTO `comment`(`username`,`usercomment`,birthdate,img,xi) VALUES(?,?,?,?,?)";
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, users.getUsername());
            psmt.setString(2, users.getUsercomment());
            psmt.setString(3,formattedTime);
            psmt.setString(4,users.getImg());
            psmt.setString(5,users.getXin());
            result = psmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, res);
        }
        return result;
    }
    /**
     * 添加方法
     * @param users Comment对像
     * @return 返回是否存在（如存在返回1；则返回0）
     */
    @Override
    public int addUesr(Comment users) {
        Connection conn = this.getCollection();
        int a=0;
        String sql = "INSERT INTO users(`userId`,`username`,usercomment,img) VALUES(?,?,?,?)";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, users.getUsername());
            pstmt.setString(2, users.getUersname());
            pstmt.setString(3, users.getComment());
            pstmt.setString(4, users.getImg());
            pstmt.executeUpdate();
            a= pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return a;
    }

    @Override
    public boolean saveReply(Reply reply) {
        String sql = "INSERT INTO users(`username`,usercomment,img) VALUES(?,?,?,?)";

        try (Connection conn = this.getCollection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, reply.getUsername());
            pstmt.setString(2, reply.getUsername());
            pstmt.setString(3, reply.getContent());

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 添加方法（该方法是测试版本，根据当前业务要求进行调取）
     * @param comment Comment对像
     */
    @Override
    public void addComment(Comment comment) {
        String sql = "INSERT INTO `comment`(`username`,`usercomment`,birthdate,img) VALUES(?,?,?,?)";

        try (Connection conn = this.getCollection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, comment.getUsername());
            pstmt.setString(2, comment.getUsercomment());
            pstmt.setString(3, comment.getBirthdate());
            pstmt.setString(4, comment.getImg());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**获取Comment对象的数据进行添加
     * @param users Comment对像
     * @return 返回是否存在（如存在返回1；则返回0）
     */

    @Override
    public int addUsers2(Comment users) {
        Connection conn = this.getCollection();
        PreparedStatement psmt = null;
        ResultSet res = null;
        int result = 0;
        try {
            // 获取用户ID
            Integer userId =getUserIdByUsername(users.getUersname());
            if (userId == 0) {
                throw new RuntimeException("该用户不存在");
            }
            String sql = "INSERT INTO `users`(`user_id`, `username`, `usercomment`, img) VALUES(?, ?, ?, ?)";
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, userId);
            psmt.setString(2, users.getUersname());
            psmt.setString(3, users.getUsercomment());
            psmt.setString(4, users.getImg());
            result = psmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, res);
        }
        return result;
    }

    /**
     * 通过用户名获取用户数据
     * @param name 获取的用户名
     * @return 返回是否存在该用户（如果存在则获取当前用户"id"）
     */
    @Override
        public int getUserIdByUsername(String name) {
        Comment comment=new Comment();
        Connection conn = this.getCollection();
        String sql = "select * from users where `username`=?";
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setObject(1, comment.getUsername());
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                return rs.getInt("id");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return 0;
    }

    /**
     * 前端获取数据的方法（将数据封装到HotelReview）发给前端后台页面进行管理
     * @param start 页码
     * @param limit 总数
     * @return 返回封装的HotelReview
     */
    @Override
    public List<HotelReview> getHotelReview(int start, int limit) {
        String sql = "SELECT * FROM `comment` LIMIT ?, ?";
        Connection conn=this.getCollection();
        try {
            PreparedStatement preparedStatement =conn.prepareStatement(sql);
            preparedStatement.setInt(1, start);
            preparedStatement.setInt(2, limit);
            ResultSet rs = preparedStatement.executeQuery();
            List<HotelReview> hotelReviewList = new ArrayList<>();
            while (rs.next()) {
                HotelReview hotelReview = new HotelReview();
                hotelReview.setId(rs.getLong("id"));
                hotelReview.setUsername(rs.getString("username"));
                hotelReview.setRating(rs.getString("xi"));
                hotelReview.setComment(rs.getString("usercomment"));
                hotelReview.setImage(rs.getString("img"));
                hotelReviewList.add(hotelReview);
            }
            return hotelReviewList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.closeAll(conn, null, null);
        }

    }

    /**
     * 获取进度条
     * @return 返回改评论的数量
     */
    @Override
    public Xingon getXi() {
        String sql="select `xi` from `comment`";
        Connection conn=this.getCollection();
        Xingon xingon=new Xingon();
        int ttup=0;//计时器
        int ttut=0;//计时器
        int ttuu=0;//计时器
        try {
            PreparedStatement psmt=conn.prepareStatement(sql);
            ResultSet rs=psmt.executeQuery();
            label:
            while(rs.next()){
                String commentType = rs.getString(1);
                switch (commentType) {
                    case "好评":
                        ttup++;
                        xingon.setHaopin(ttup);
                        continue ;
                    case "差评":
                        ttut++;
                        xingon.setChapin(ttut);
                        continue ;
                    case "中评":
                        ttuu++;
                        xingon.setZongpin(ttuu);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,null,null);
        }
        return xingon;
    }

    @Override
    public int getPageSize(int id) {//查询总数居,返回数据大小
        String sql="select count(*) from `comment`";
        Connection connection=this.getCollection();
        try {
            PreparedStatement psmt=connection.prepareStatement(sql);
            ResultSet rs=psmt.executeQuery();
            if(rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return 0;
    }

    /**
     * 根据用户名查找xiaox
     * @param name 用户名
     * @return 用户信息
     */
    @Override
    public Comment getUsers(String name) {
        Comment users = new Comment();
        Connection connection = this.getCollection();
        String sql = "select * from comment where `username`=?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setObject(1, name);//把姓名加入到差数中，替换？
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {//是true说明查到了一条数据
                users.setId(rs.getInt("id"));
                users.setUersname(rs.getString("username"));
                users.setUsercomment(rs.getString("usercomment"));
                users.setBirthdate(rs.getString("birthdate"));
                users.setImg(rs.getString("img"));
                return users;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    /**
     * 该方法用于分页查询，通过数据库的调查好评等。。数据获取评价总数实现进度条（测试版）
     * （注；！！！该方法可删）
     * @param start 起始页
     * @param limit 总页数
     * @param xi 评价信息
     * @return 返回一个集合
     */
    @Override
    public List<Comment> getXi(int start, int limit,String xi) {
        //创建Comment一个集合
        List<Comment> comments=new ArrayList<>();
        //写一个sql语句
        String sql="SELECT id, username, usercomment, birthdate, img,xi   FROM `comment` where xi=? LIMIT ?, ?   ";
        Connection conn=this.getCollection();//连接数据库
        PreparedStatement preparedStatement=null;
        try {
             preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setString(1,xi);
            preparedStatement.setInt(2, start);
            preparedStatement.setInt(3, limit);

            ResultSet rs=preparedStatement.executeQuery();
            while(rs.next()){
                Comment comment = new Comment();
                comment.setId(rs.getInt("id"));
                comment.setUersname(rs.getString("username"));
                comment.setUsercomment(rs.getString("usercomment"));
                comment.setBirthdate(rs.getString("birthdate"));
                comment.setImg(rs.getString("img"));
                comments.add(comment);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn,preparedStatement,null);
        }

        return comments;
    }
/**
 * 获取`username`, `usercomment`,`img`三条数据数据
 *该数据仅使用用户命名，评论，图片进行逻辑编译时调取
 * @return 返回查询结果
 */
    public List<Comment> getAllComment() {
        List<Comment> comments = new ArrayList<>();
        String sql = "SELECT `username`, `usercomment`,`img` ,FROM `users`";

        try (  Connection conn = this.getCollection();
               PreparedStatement pstmt = conn.prepareStatement(sql);
               ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                Comment comment = new Comment();
                comment.setUersname(rs.getString("username"));
                comment.setUsercomment(rs.getString("usercomment"));
                comment.setImg(rs.getString("img"));
                comments.add(comment);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return comments;
    }

    /**
     * 获取所有数据
     * @return 返回查询就结果
     */
    @Override
    public List<Comment> getAllComments() {
        List<Comment> comments = new ArrayList<>();
        String sql = "SELECT`id`,`username`, `usercomment`, `birthdate`, `img` FROM `comment`";

        try (  Connection conn = this.getCollection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                Comment comment = new Comment();
                comment.setId(rs.getInt("id"));
                comment.setUersname(rs.getString("username"));
                comment.setUsercomment(rs.getString("usercomment"));
                comment.setBirthdate(rs.getString("birthdate"));
                comment.setImg(rs.getString("img"));
                comments.add(comment);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return comments;
        }

    /**
     * 通过用户命查询数据
     * @param name 用户命
     * @return 用户信息
     */
    @Override
    public List<Comment> getName(String name) {
        List<Comment> comments = new ArrayList<>();
        String sql="SELECT `username`, `usercomment`, `birthdate`, `img` FROM `comment` where username=?";
        Connection connection=this.getCollection();
        try {
            PreparedStatement psmt=connection.prepareStatement(sql);
            psmt.setString(1,name);
            ResultSet rs=psmt.executeQuery();
            while (rs.next()){
                Comment comment=new Comment();
                comment.setUersname(rs.getString("username"));
                comment.setUsercomment(rs.getString("usercomment"));
                comment.setBirthdate(rs.getString("birthdate"));
                comment.setImg(rs.getString("img"));
                comments.add(comment);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return comments;
    }

//    @Override
//    public int nameId(String name) {
//       Connection conn= this.getCollection();
//        String sql="SELECT `id` FROM `users` where username=?";
//        try {
//            PreparedStatement psmt= conn.prepareStatement(sql);
//            psmt.setString(1,name);
//            ResultSet rs=psmt.executeQuery();
//            if (rs.next()){
//                Comment comment=new Comment();
//                comment.getUserId(rs.getInt("id"));
//                return rs.getInt("id");
//            }
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
//
//        return 0;
//    }

    /**
     * 通过参数删除评论
     * @param commentId 评论的id
     * @return 返回删除的布尔值（true：删除成功，false：删除失败）
     */
        public  boolean deleteComment(int commentId) {
            Connection conn = this.getCollection();
            PreparedStatement stmt=null;
            int rowsAffected = 0;
            try {
                String sql = "DELETE FROM comment WHERE id = ?";
                stmt = conn.prepareStatement(sql);
                stmt.setInt(1, commentId);
                rowsAffected = stmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                this.closeAll(conn, stmt, null);
            }
            return rowsAffected > 0;
        }

    /**
     *获得酒店的分页查询数据
     * @param start 起始记录
     * @param limit 总页数
     * @return 返回查到的数据
     */
    @Override
    public List<Comment> getComments(int start, int limit){
        //调用Comment的集合获取里面的数据
        List<Comment> comments = new ArrayList<>();
        String sql = "SELECT id, username, usercomment, birthdate, img,xi FROM `comment` order by id desc LIMIT ?, ?";

        try (Connection conn = this.getCollection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql)) {

            preparedStatement.setInt(1, start);
            preparedStatement.setInt(2, limit);

            try (ResultSet rs = preparedStatement.executeQuery()) {
                while (rs.next()) {
                    Comment comment = new Comment();
                    comment.setId(rs.getInt("id"));
                    comment.setUersname(rs.getString("username"));
                    comment.setUsercomment(rs.getString("usercomment"));
                    comment.setBirthdate(rs.getString("birthdate"));
                    comment.setImg(rs.getString("img"));
                    comments.add(comment);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return comments;
    }

    @Override
    public int getTotalPages(int pageSize) {
        int totalComments = 0;
        totalComments = getTotalCommentsCount1();
        return (int) Math.ceil((double) totalComments / pageSize);
    }

    public int getTotalCommentsCount1() {
        int totalComments = 0;
        String sql = "SELECT COUNT(*) FROM `comment`";
        try {
            try (Connection conn =this.getCollection();
                 PreparedStatement ps = conn.prepareStatement(sql);
                 ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    totalComments = rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return totalComments;
    }

    /**
     * 景点分页查询
     * @param start 起始记录
     * @param limit 总页数
     * @return 返回查询结果
     */
    @Override
    public List<scenicSpots> getAllComments(int start, int limit) {

     Connection conn=this.getCollection();
     String sql="SELECT `scenic_id`, `scenic_xi`, `scenic_comments`, `scenic_img`,`scenic_img1`,`scenic_img2`,`scenic_img3` FROM `scenic` LIMIT ?, ?";
     List<scenicSpots> comments = new ArrayList<>();
     try {
         PreparedStatement psmt=conn.prepareStatement(sql);
         psmt.setInt(1,start);
         psmt.setInt(2,limit);
         ResultSet rs=psmt.executeQuery();
         while (rs.next()){
             scenicSpots scenicSpots=new scenicSpots();
             scenicSpots.setScenic_id(rs.getInt("scenic_id"));
             scenicSpots.setScenic_xi(rs.getString("scenic_xi"));
             scenicSpots.setScenic_Comments(rs.getString("scenic_comments"));
             scenicSpots.setScenic_img(rs.getString("scenic_img"));
             scenicSpots.setScenic_img1(rs.getString( "scenic_img1"));
             scenicSpots.setScenic_img2(rs.getString("scenic_img2"));
             scenicSpots.setScenic_img3(rs.getString("scenic_img3"));
             comments.add(scenicSpots);
         }
         return comments;
         } catch (SQLException e) {
             throw new RuntimeException(e);
         }
     }
    }

